package com.lagou.dao.impl;

import com.lagou.dao.CourseContentDao;
import com.lagou.pojo.Course;
import com.lagou.pojo.Course_Lesson;
import com.lagou.pojo.Course_Section;
import com.lagou.utils.DateUtils;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 课程管理接口Dao层的实现类
 */
public class CourseContentDaoImpl implements CourseContentDao {
    //根据课程ID查询课时相关信息
    @Override
    public List<Course_Section> findSectionAndLessonByCourseId(int courseId) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.编写sql
            String sql = "SELECT\n" +
                    " id,\n" +
                    " course_id,\n" +
                    " section_name,\n" +
                    " description,\n" +
                    " order_num,\n" +
                    " STATUS\n" +
                    "FROM course_section WHERE course_id = ?";
            //执行查询
            List<Course_Section> sectionList = qr.query(sql, new BeanListHandler<Course_Section>(Course_Section.class), courseId);
            for (Course_Section section : sectionList) {
                //调用方法 获取章节对应的课时
                List<Course_Lesson> lessonList = findLessonBySectionId(section.getId());
                //将课时数据封装到 章节对象中
                section.setLessonList(lessonList);
            }
            return sectionList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }

    }

    //根据章节id 查询章节相关的课时信息
    @Override
    public List<Course_Lesson> findLessonBySectionId(int sectionId) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.编写sql
            String sql = "SELECT\n" +
                    " id,\n" +
                    " course_id,\n" +
                    " section_id,\n" +
                    " theme,\n" +
                    " duration,\n" +
                    " is_free,\n" +
                    " order_num,\n" +
                    " STATUS\n" +
                    " FROM course_lesson WHERE section_id = ?";
            List<Course_Lesson> lessonList = qr.query(sql, new BeanListHandler<Course_Lesson>(Course_Lesson.class), sectionId);
            return lessonList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }

    //添加章节时进行数据回显
    @Override
    public Course findCourseByCourseId(int courseId) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.先写SQL
            String sql = "SELECT\n" +
                    "id,\n" +
                    "course_name\n" +
                    "FROM course WHERE id=?";
            Course course = qr.query(sql, new BeanHandler<>(Course.class), courseId);
            return course;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }

    //保存章节信息
    @Override
    public int saveSection(Course_Section section) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.先写SQL
            String sql = "INSERT INTO course_section (course_id,\n" +
                    "section_name,\n" +
                    "description,\n" +
                    "order_num,\n" +
                    "STATUS,\n" +
                    "create_time,\n" +
                    "update_time)VALUES(?,?,?,?,?,?,?);";
            Object[] param =
                    {section.getCourse_id(), section.getSection_name(), section.getDescription(),
                            section.getOrder_num(), section.getStatus(), section.getCreate_time(), section.getUpdate_time()};
            int row = qr.update(sql, param);
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 0;
        }

    }

    //修改章节
    @Override
    public int updateSection(Course_Section section) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.编写sql 修改操作
            String sql = "UPDATE course_section SET\n" +
                    "section_name = ?,\n" +
                    "description = ?,\n" +
                    "order_num = ?,\n" +
                    "update_time = ? WHERE id = ?;";
            Object[] param =
                    {section.getSection_name(), section.getDescription(), section.getOrder_num(),
                            section.getUpdate_time(), section.getId()};
            int row = qr.update(sql, param);
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 0;
        }

    }
    //修改章节状态
    @Override
    public int updateSectionStatus(int id, int status) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.编写sql 修改操作
            String sql = "update course_section set status= ?,update_time= ? where id=?";
            Object[] param ={status, DateUtils.getDateFormart(),id};
            int row = qr.update(sql, param);
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 0;
        }
    }
//保存课时信息
    @Override
    public int saveLesson(Course_Lesson lesson) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.编写sql
            String sql = "INSERT INTO course_lesson(course_id,section_id,theme,duration,is_free,order_num,create_time,update_time)VALUES(?,?,?,?,?,?,?,?)";
            Object[] param = {lesson.getCourse_id(),lesson.getSection_id(),lesson.getTheme(),lesson.getDuration(),lesson.getIs_free(),lesson.getOrderNum(),
            lesson.getCreate_time(),lesson.getUpdate_time()};
            //3.执行操作
            int row = qr.update(sql, param);
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 0;
        }

    }
    //修改课时信息
    @Override
    public int updateLesson(Course_Lesson lesson) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            //2.编写sql
            String sql = "UPDATE course_lesson SET course_id=?,section_id=?,theme=?,duration=?,is_free=?,order_num=?,update_time=? WHERE id=?";
            Object[] param={lesson.getCourse_id(),lesson.getSection_id(),lesson.getTheme(),lesson.getDuration(),lesson.getIs_free(),lesson.getOrderNum(),
                    lesson.getUpdate_time(),lesson.getId()};
            int row = qr.update(sql, param);
            return  row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return 0;
        }
    }
}
